#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection

from web.constants.direction import Direction
from web.dao.base_dao import BaseDao
from web.manager.log_manager import LogManager
from web.models import CommodityFutureInfo
from web.models.quant2_commodity_future_transaction_record import Quant2CommodityFutureTransactionRecord
from web.util.fee_util import FeeUtil

Logger = LogManager.get_logger(__name__)

class Quant2CommodityFutureTransactionRecordDao(BaseDao):
    """
    Quant2CommodityFutureTransactionRecord的dao类
    """

    model_class = Quant2CommodityFutureTransactionRecord

    def find_by_account_name_and_not_close_position(self, account_name:str) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        根据account_name，查询正在持仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t " \
                        "where t.account_name = '{}' and ((t.direction = 1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                         "or (t.direction = -1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null))".format(account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def find_not_close_position(self) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        查询正在持仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t " \
                        "where ((t.direction = 1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                         "or (t.direction = -1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null))"
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def find_current_close_position_by_account_name(self, account_name: str, transaction_date: str) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        根据account_name，查询当天被平仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t " \
                       "where t.account_name = '{}' and ((t.direction = 1 and t.buy_date is not null and t.sell_date = to_date({}, 'yyyy-mm-dd'))" \
                       "or (t.direction = -1 and t.sell_date is not null and t.buy_date = to_date({}, 'yyyy-mm-dd')))".format(
                account_name, transaction_date, transaction_date)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)


    def insert_when_open_long_position(self, account_name:str, code: str, transaction_date: str, close_price: float, open_or_close_position_lot: int,
                                       commodity_future_info: CommodityFutureInfo, direction: int, k_line_movement_pattern: int = None):
        """
        开多仓
        """
        with connection.cursor() as cursor:
            sql: str = None
            if k_line_movement_pattern is not None:
                sql = "insert into quant2_c_f_transact_record(ACCOUNT_NAME, CODE, BUY_DATE, BUY_PRICE, BUY_LOT, " \
                      "DIRECTION, OPEN_COMMISSION, K_LINE_MOVEMENT_PATTERN) " \
                      "values('{}', '{}', to_date({}, 'yyyy-mm-dd'), {}, {}, {}, {}, {})".format(account_name, code, transaction_date, close_price, open_or_close_position_lot,
                        direction, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, open_or_close_position_lot), k_line_movement_pattern)
            else:
                sql = "insert into quant2_c_f_transact_record(ACCOUNT_NAME, CODE, BUY_DATE, BUY_PRICE, BUY_LOT, " \
						"DIRECTION, OPEN_COMMISSION) " \
						"values('{}', '{}', to_date({}, 'yyyy-mm-dd'), {}, {}, {}, {})".format(account_name, code, transaction_date, close_price, open_or_close_position_lot,
                        direction, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, open_or_close_position_lot))
            cursor.execute(sql)

    def insert_when_open_short_position(self, account_name: str, code: str, transaction_date: str, close_price: float, open_or_close_position_lot: int,
                                        commodity_future_info: CommodityFutureInfo, direction: int, k_line_movement_pattern: int = None):
        """
        开多仓
        """
        with connection.cursor() as cursor:
            sql: str = None
            if k_line_movement_pattern is not None:
                sql = "insert into quant2_c_f_transact_record(ACCOUNT_NAME, CODE, SELL_DATE, SELL_PRICE, SELL_LOT, " \
                      "DIRECTION, OPEN_COMMISSION, K_LINE_MOVEMENT_PATTERN) " \
                      "values('{}', '{}', to_date({}, 'yyyy-mm-dd'), {}, {}, {}, {}, {})".format(account_name, code, transaction_date, close_price, open_or_close_position_lot,
                        direction, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, open_or_close_position_lot), k_line_movement_pattern)
            else:
                sql = "insert into quant2_c_f_transact_record(ACCOUNT_NAME, CODE, SELL_DATE, SELL_PRICE, SELL_LOT, " \
                      "DIRECTION, OPEN_COMMISSION) " \
                      "values('{}', '{}', to_date({}, 'yyyy-mm-dd'), {}, {}, {}, {})".format(account_name, code, transaction_date, close_price, open_or_close_position_lot,
                        direction, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, open_or_close_position_lot))
            cursor.execute(sql)

    def find_by_account_name_and_not_close_position_and_not_in_quant2_c_f_filter(self, account_name:str) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        根据account_name，查询正在持仓的期货，并且不在表quant2_c_f_filter中
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t " \
                        "where t.account_name = '{}' and ((t.direction = 1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                         "or (t.direction = -1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null)) and " \
                         "t.code not in(select t1.code from quant2_c_f_filter t1)".format(account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def find_close_position_by_date(self, date):
        """
        查询当天平仓的期货
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t where (t.direction = 1 and t.buy_date is not null and t.sell_date=to_date('{}','yyyy-mm-dd')) \
                            or (t.direction = -1 and t.sell_date is not null and t.buy_date=to_date('{}','yyyy-mm-dd'))".format(date, date)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def find_current_open_position_and_not_hold_in_optimize2_commodity_future_transaction_record_by_date_account_name(self, date: str, account_name: str) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        根据账号，查询当天新开仓的期货，并且在表optimize2_c_f_transact_record表中没有持仓的记录
        """

        with connection.cursor() as cursor:
            sql: str = "select * from quant2_c_f_transact_record t where t.account_name = '{}' and  ((t.direction = 1 and t.buy_date = to_date('{}', 'yyyy-mm-dd') and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                "or (t.direction = -1 and t.sell_date = to_date('{}', 'yyyy-mm-dd') and t.buy_date is null and t.buy_price is null and t.buy_lot is null)) " \
                "and t.code not in(select t2.code from optimize2_c_f_transact_record t2 where t2.account_name = '{}' and ((t2.direction = 1 and t2.sell_date is null and t2.sell_price is null and t2.sell_lot is null) " \
                "or (t2.direction = -1 and t2.buy_date is null and t2.buy_price is null and t2.buy_lot is null)))".format(account_name, date, date, account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def find_by_account_name_and_hold_in_quant2_commodity_future_transaction_record_and_not_hold_in_optimize2_commodity_future_transaction_record(self, account_name: str, date: str):
        """
        根据账号查询期货，要求在quant2_c_f_transact_record中持仓，但在optimize2_c_f_transact_record表中没持仓
        """

        with connection.cursor() as cursor:
            sql: str = "select t.* from quant2_c_f_transact_record t " \
                    "join c_f_date_contract_data cfdcd on cfdcd.code=t.code and cfdcd.transaction_date=to_date({}, 'yyyy-mm-dd') " \
                    "where t.account_name='{}' and ((t.direction=1 and t.sell_date is null and t.sell_price is null and t.sell_lot is null) " \
                    "or (t.direction=-1 and t.buy_date is null and t.buy_price is null and t.buy_lot is null)) " \
                    "and t.code not in(" \
					"select t1.code from optimize2_c_f_transact_record t1 " \
					"where ((t1.direction=1 and t1.sell_date is null and t1.sell_price is null and t1.sell_lot is null) " \
                    "or (t1.direction=-1 and t1.buy_date is null and t1.buy_price is null and t1.buy_lot is null))) " \
                    "order by case when t.direction=1 then (cfdcd.close_price - t.buy_price)/t.buy_price*100 " \
                    "when t.direction=-1 then (t.sell_price - cfdcd.close_price)/t.sell_price*100 end asc".format(date, account_name)
            cursor.execute(sql)
            _list = cursor.fetchall()
            return self.tuple_list_to_quant2_commodity_future_transaction_record_list(_list)

    def update_when_close_position(self, direction: int, transaction_date: str, close_price: float, commodity_future_info: CommodityFutureInfo, lot: int, close_position_reason: str, id: int):
        """
        更新quant2_c_f_transact_record表的sell_date/buy_date、sell_price/buy_price、sell_lot/buy_lot、profit_and_loss和profit_and_loss_rate字段
        """

        with connection.cursor() as cursor:
            sql: str = None
            if direction == Direction.Up:
                sql = "update quant2_c_f_transact_record t " \
                    "set t.sell_date = to_date({}, 'yyyy-mm-dd'), t.sell_price  = {}, t.sell_lot = t.buy_lot, " \
                    "t.open_commission = {}, " \
                    "t.close_commission = {}, " \
                    "t.close_position_reason = {} " \
                    "where t.id_ = {}".format(transaction_date, close_price, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, lot),
                                              FeeUtil.calculate_close_old_commodity_future_fee(close_price, commodity_future_info, lot), close_position_reason, id)
                cursor.execute(sql)

                sql = "update quant2_c_f_transact_record t " \
                      "set t.profit_and_loss = ROUND((t.sell_price - t.buy_price) * t.buy_lot * {}, 4), " \
                      "t.profit_and_loss_rate = ((t.sell_price - t.buy_price) * t.buy_lot * {}) / (t.buy_price * t.buy_lot * {}) * 100 " \
				    "where t.id_ = {}".format(commodity_future_info.transaction_multiplier,
                                              commodity_future_info.transaction_multiplier, commodity_future_info.transaction_multiplier,
                                              id)
                cursor.execute(sql)

            if direction == Direction.Down:
                sql = "update quant2_c_f_transact_record t " \
                    "set t.buy_date = to_date({}, 'yyyy-mm-dd'), t.buy_price  = {}, t.buy_lot = t.sell_lot, " \
                    "t.open_commission = {}, " \
                    "t.close_commission = {}, " \
                    "t.close_position_reason = {} " \
                    "where t.id_ = {}".format(transaction_date, close_price, FeeUtil.calculate_open_commodity_future_fee(close_price, commodity_future_info, lot),
                                              FeeUtil.calculate_close_old_commodity_future_fee(close_price, commodity_future_info, lot), close_position_reason, id)
                cursor.execute(sql)

                sql = "update quant2_c_f_transact_record t " \
                      "set t.profit_and_loss = -ROUND((t.buy_price - t.sell_price) * t.sell_lot * {}, 4), " \
                      "t.profit_and_loss_rate = -((t.buy_price - t.sell_price) * t.sell_lot * {}) / (t.sell_price * t.sell_lot * {}) * 100 " \
                      "where t.id_ = {}".format(commodity_future_info.transaction_multiplier,
                                            commodity_future_info.transaction_multiplier, commodity_future_info.transaction_multiplier,
                                              id)
                cursor.execute(sql)

    def sum_open_commission_by_account_name(self, account_name: str) -> float:
        """
        根据账户名称，计算所有期货（包括正在持仓的和已经平仓的）的open_commission的和
        """

        with connection.cursor() as cursor:
            sql: str = "select COALESCE(sum(t1.open_commission), 0) from quant2_c_f_transact_record t1 " \
			"where t1.account_name = '{}'".format(account_name)
            cursor.execute(sql)
            sum_open_commission: float = cursor.fetchone()
            return sum_open_commission[0]

    def sum_close_commission_by_account_name(self, account_name: str) -> float:
        """
        根据账户名称，计算所有期货（包括正在持仓的和已经平仓的）的close_commission的和
        """

        with connection.cursor() as cursor:
            sql: str = "select COALESCE(sum(t1.close_commission), 0) from quant2_c_f_transact_record t1 " \
                       "where t1.account_name = '{}'".format(account_name)
            cursor.execute(sql)
            sum_close_commission: float = cursor.fetchone()
            return sum_close_commission[0]

    def sum_open_commission_by_account_name_and_date(self, account_name: str, transaction_date: str) -> float:
        """
        根据账户名称和日期，计算某一天的开仓费用（open_commission，包括做多和做空）之和
        """

        with connection.cursor() as cursor:
            sql: str = None
            sql = "select COALESCE(sum(t1.open_commission), 0) from quant2_c_f_transact_record t1 " \
                    "where t1.account_name = '{}' " \
                     "and ((t1.direction = 1 and t1.buy_date=to_date({}, 'yyyy-mm-dd') and t1.sell_date is null)" \
                        " or (t1.direction = -1 and t1.sell_date=to_date({}, 'yyyy-mm-dd') and t1.buy_date is null))".format(
                account_name, transaction_date, transaction_date)
            cursor.execute(sql)
            sum_open_commission: float = cursor.fetchone()
            return sum_open_commission[0]

    def calculate_holding_position_date(self, direction: int, account_name: str, code: str) -> int:
        """
        计算持仓日期
        """

        if direction == Direction.Up:
            with connection.cursor() as cursor:
                sql: str = "select count(*) from quant2_account_log t1 where t1.account_name='{}' and t1.date_=(" \
                            "select t.buy_date from quant2_c_f_transact_record t where t.code={} and t.account_name='{}' " \
                            "and t.buy_date is not null and t.buy_price is not null and t.buy_lot is not null " \
                             "and t.sell_date is null and t.sell_price is null and t.sell_lot is null)".format(account_name, code, account_name)
                cursor.execute(sql)
                _tuple: float = cursor.fetchone()
                return _tuple[0]
        if direction == Direction.Down:
            with connection.cursor() as cursor:
                sql: str = "select count(*) from quant2_account_log t1 where t1.account_name='{}' and t1.date_=(" \
                            "select t.sell_date from quant2_c_f_transact_record t where t.code={} and t.account_name='{}' " \
                            "and t.buy_date is null and t.buy_price is null and t.buy_lot is null " \
                            "and t.sell_date is not null and t.sell_price is not null and t.sell_lot is not null)".format(account_name, code, account_name)
                cursor.execute(sql)
                _tuple: float = cursor.fetchone()
                return _tuple[0]

    def tuple_list_to_quant2_commodity_future_transaction_record_list(self, _list) -> list[Quant2CommodityFutureTransactionRecord]:
        """
        将list[tuple]类型转换为list[Quant2CommodityFutureTransactionRecord]类型
        """

        if _list is not None and len(_list) > 0:
            quant2_commodity_future_transaction_record_list: list[Quant2CommodityFutureTransactionRecord] = list()
            for row in _list:
                quant2_commodity_future_transaction_record: Quant2CommodityFutureTransactionRecord = Quant2CommodityFutureTransactionRecord()
                quant2_commodity_future_transaction_record.id_ = row[0]
                quant2_commodity_future_transaction_record.account_name = row[1]
                quant2_commodity_future_transaction_record.code = row[2]
                quant2_commodity_future_transaction_record.buy_date = row[3]
                quant2_commodity_future_transaction_record.buy_price = row[4]
                quant2_commodity_future_transaction_record.buy_lot = row[5]
                quant2_commodity_future_transaction_record.sell_date = row[6]
                quant2_commodity_future_transaction_record.sell_price = row[7]
                quant2_commodity_future_transaction_record.sell_lot = row[8]
                quant2_commodity_future_transaction_record.direction = row[9]
                quant2_commodity_future_transaction_record.profit_and_loss = row[10]
                quant2_commodity_future_transaction_record.profit_and_loss_rate = row[11]
                quant2_commodity_future_transaction_record.open_commission = row[12]
                quant2_commodity_future_transaction_record.close_commission = row[13]
                quant2_commodity_future_transaction_record.k_line_movement_pattern = row[14]
                quant2_commodity_future_transaction_record_list.append(quant2_commodity_future_transaction_record)
            return quant2_commodity_future_transaction_record_list
        else:
            None

    def calculate_current_long_position_number_and_short_position_number(self):
        """
        计算当前日期持有的多单和空单的数量
        """

        with connection.cursor() as cursor:
            hold_long_position_number_sql: str = "select count(*) from quant2_c_f_transact_record t1 " \
                    "where t1.direction = 1 and t1.buy_date is not null and t1.buy_price is not null and t1.buy_lot is not null " \
                        "and t1.sell_date is null and t1.sell_price is null and t1.sell_lot is null"
            cursor.execute(hold_long_position_number_sql)
            holding_long_position_number: tuple = cursor.fetchone()

            hold_short_position_number_sql: str = "select count(*) from quant2_c_f_transact_record t1 " \
                                            "where t1.direction = -1 and t1.buy_date is null and t1.buy_price is null and t1.buy_lot is null " \
                                            "and t1.sell_date is not null and t1.sell_price is not null and t1.sell_lot is not null"
            cursor.execute(hold_short_position_number_sql)
            hold_short_position_number: tuple = cursor.fetchone()

            return holding_long_position_number[0], hold_short_position_number[0]

    def calculate_close_long_position_number_by_date(self, date: str):
        """
        根据日期，计算平多仓数量
        """

        with connection.cursor() as cursor:
            close_long_position_number_sql: str = "select count(*) from quant2_c_f_transact_record t1 " \
                                                 "where t1.direction = 1 and t1.sell_date=to_date({}, 'yyyy-mm-dd')" \
                                                  "and t1.buy_date is not null and t1.buy_price is not null and t1.buy_lot is not null " \
                                                 "and t1.sell_date is not null and t1.sell_price is not null and t1.sell_lot is not null".format(date)
            cursor.execute(close_long_position_number_sql)
            close_long_position_number: tuple = cursor.fetchone()
            return close_long_position_number[0]

    def calculate_close_short_position_number_by_date(self, date: str):
        """
        根据日期，计算平空仓数量
        """

        with connection.cursor() as cursor:
            close_short_position_number_sql: str = "select count(*) from quant2_c_f_transact_record t1 " \
                                                 "where t1.direction = -1 and t1.buy_date=to_date({}, 'yyyy-mm-dd')" \
                                                  "and t1.buy_date is not null and t1.buy_price is not null and t1.buy_lot is not null " \
                                                 "and t1.sell_date is not null and t1.sell_price is not null and t1.sell_lot is not null".format(date)
            cursor.execute(close_short_position_number_sql)
            close_short_position_number: tuple = cursor.fetchone()
            return close_short_position_number[0]